package com.example.mqttclient;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Locale;

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "carbon_footprint.db";
    private static final int DATABASE_VERSION = 1;

    private static final String TABLE_DEVICE_USAGE = "device_usage";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_DEVICE = "device";
    private static final String COLUMN_START_TIME = "start_time";
    private static final String COLUMN_END_TIME = "end_time";
    private static final String COLUMN_ENERGY = "energy";
    private static final String COLUMN_EMISSION = "emission";

    private static final String CREATE_TABLE_DEVICE_USAGE =
            "CREATE TABLE " + TABLE_DEVICE_USAGE + "(" +
                    COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    COLUMN_DEVICE + " TEXT," +
                    COLUMN_START_TIME + " INTEGER," +
                    COLUMN_END_TIME + " INTEGER," +
                    COLUMN_ENERGY + " REAL," +
                    COLUMN_EMISSION + " REAL)";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_DEVICE_USAGE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_DEVICE_USAGE);
        onCreate(db);
    }

    public long addDeviceUsage(String device, long startTime, long endTime, double energy, double emission) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_DEVICE, device);
        values.put(COLUMN_START_TIME, startTime);
        values.put(COLUMN_END_TIME, endTime);
        values.put(COLUMN_ENERGY, energy);
        values.put(COLUMN_EMISSION, emission);
        return db.insert(TABLE_DEVICE_USAGE, null, values);
    }
// 在DatabaseHelper中添加

    public void bulkInsertDeviceUsage(List<DeviceUsageRecord> records) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.beginTransaction();
        try {
            for (DeviceUsageRecord record : records) {
                ContentValues values = new ContentValues();
                values.put(COLUMN_DEVICE, record.getDevice());
                values.put(COLUMN_START_TIME, record.getStartTime());
                values.put(COLUMN_END_TIME, record.getEndTime());
                values.put(COLUMN_ENERGY, record.getEnergy());
                values.put(COLUMN_EMISSION, record.getEmission());
                db.insert(TABLE_DEVICE_USAGE, null, values);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }
    public List<DeviceUsageRecord> getAllRecords() {
        List<DeviceUsageRecord> records = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_DEVICE_USAGE,
                new String[]{COLUMN_ID, COLUMN_DEVICE, COLUMN_START_TIME, COLUMN_END_TIME, COLUMN_ENERGY, COLUMN_EMISSION},
                null, null, null, null, COLUMN_START_TIME + " DESC");

        if (cursor.moveToFirst()) {
            do {
                DeviceUsageRecord record = new DeviceUsageRecord();
                record.setId(cursor.getLong(0));
                record.setDevice(cursor.getString(1));
                record.setStartTime(cursor.getLong(2));
                record.setEndTime(cursor.getLong(3));
                record.setEnergy(cursor.getDouble(4));
                record.setEmission(cursor.getDouble(5));
                records.add(record);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return records;
    }


    // 添加以下新方法

    public List<DeviceUsageRecord> getTodayRecords() {
        long todayStart = getStartOfDay(System.currentTimeMillis());
        return getRecordsAfter(todayStart);
    }

    public List<DeviceUsageRecord> getWeeklyRecords() {
        long weekStart = getStartOfWeek(System.currentTimeMillis());
        return getRecordsAfter(weekStart);
    }

    private List<DeviceUsageRecord> getRecordsAfter(long timestamp) {
        List<DeviceUsageRecord> records = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        String query = "SELECT * FROM " + TABLE_DEVICE_USAGE +
                " WHERE " + COLUMN_START_TIME + " >= ?" +
                " ORDER BY " + COLUMN_START_TIME + " DESC";

        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(timestamp)});

        // 处理cursor获取记录...
        return records;
    }

    private long getStartOfDay(long timeInMillis) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTimeInMillis(timeInMillis);
        calendar.set(Calendar.HOUR_OF_DAY, 0);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.SECOND, 0);
        calendar.set(Calendar.MILLISECOND, 0);
        return calendar.getTimeInMillis();
    }

    private long getStartOfWeek(long timeInMillis) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTimeInMillis(timeInMillis);
        calendar.set(Calendar.DAY_OF_WEEK, calendar.getFirstDayOfWeek());
        calendar.set(Calendar.HOUR_OF_DAY, 0);
        // ...其他时间字段清零...
        return calendar.getTimeInMillis();
    }
    public List<DeviceUsageRecord> getHourlyRecords() {
        List<DeviceUsageRecord> records = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        String query = "SELECT " +
                "strftime('%Y-%m-%d %H:00', datetime(" + COLUMN_START_TIME + "/1000, 'unixepoch')) as hour, " +
                "SUM(" + COLUMN_ENERGY + ") as total_energy, " +
                "SUM(" + COLUMN_EMISSION + ") as total_emission " +
                "FROM " + TABLE_DEVICE_USAGE + " " +
                "GROUP BY hour " +
                "ORDER BY hour DESC";

        Cursor cursor = db.rawQuery(query, null);

        if (cursor.moveToFirst()) {
            do {
                DeviceUsageRecord record = new DeviceUsageRecord();
                record.setDevice("每小时汇总");
                record.setEnergy(cursor.getDouble(1));
                record.setEmission(cursor.getDouble(2));

                // 解析时间
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm", Locale.getDefault());
                try {
                    Date date = format.parse(cursor.getString(0));
                    record.setStartTime(date.getTime());
                    record.setEndTime(date.getTime() + 3600000); // 加1小时
                } catch (ParseException e) {
                    e.printStackTrace();
                }

                records.add(record);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return records;
    }
    public static class DeviceUsageRecord {
        private long id;
        private String device;
        private long startTime;
        private long endTime;
        private double energy;
        private double emission;

        // Getter方法
        public long getId() {
            return id;
        }

        public String getDevice() {
            return device;
        }

        public long getStartTime() {
            return startTime;
        }

        public long getEndTime() {
            return endTime;
        }

        public double getEnergy() {
            return energy;
        }

        public double getEmission() {
            return emission;
        }

        // Setter方法
        public void setId(long id) {
            this.id = id;
        }

        public void setDevice(String device) {
            this.device = device;
        }

        public void setStartTime(long startTime) {
            this.startTime = startTime;
        }

        public void setEndTime(long endTime) {
            this.endTime = endTime;
        }

        public void setEnergy(double energy) {
            this.energy = energy;
        }

        public void setEmission(double emission) {
            this.emission = emission;
        }
    }
}